'''
Created on Apr 1, 2012

@author: e911miri

This module is to generate an orchard database with test records
Before you run it, make the you install the dependencies, Mysql Connector/Python,
Python 3.2
Next make sure the database connection in config.py matches your actual sql server config
Then you can run this module either by double clicking on it(That worked for me) or
or running python 
'''
import os
import sys
sys.path.append(os.path.join(os.path.dirname('__file__'), 'db'))

import random

import mysql.connector
import config

import dbgen
from dbgen import combination, subjects, names
import dbquery
import string
config = config.dbConfig.dbinfo().copy()
conn = mysql.connector.Connect(**config)

def create_db():
    cursor = conn.cursor()
    query = dbquery.query
    cursor.execute(query)
    conn.commit()
    

subjs = dbgen.subjects
combo = dbgen.combination

# Ignacio's random string generator from
# http://stackoverflow.com/questions/2257441/python-random-string-generation-with-upper-case-letters-and-digits
def generate_word(size=6, chars=string.ascii_uppercase):
    return ''.join(random.choice(chars) for x in range(size))

def generate_scores():
    #scores of a particular candidate
    return random.sample(range(100), 4)

def save_combos():
    #combination of subjects
    combos = []
    for x in dbgen.combination:
        com = list(map(subjects.__getitem__, x))
        combos.append(com)
    print(combos)
    combo_query = "INSERT INTO combinations (a,b,c,d) VALUES (%s, %s, %s, %s)"
    conn = mysql.connector.Connect(**config)
    c = conn.cursor()
    c.executemany(combo_query, combos)
    conn.commit()
    print("Combos Saved to Database")

def main(num):
    create_db()
    c = conn.cursor()
    commit = []
    numcombo = len(combination)
    print("Now Generating Registration Numbers")
    cquery = '''
    INSERT INTO candidates (reg_num, pin, combination, name) VALUES (%s, %s, %s, %s);
    INSERT INTO scores (a,b,c,d) VALUES ('%s', '%s', '%s', '%s');
    '''
    for i in range(num):        
        wordLen = 10 #random.randint(5, 15)
        pin = random.randint(1000, 9999)
        word = generate_word(size=wordLen)
        if i % (num/10) == 0:            
            print("I have generated {0} numbers".format(i))
            c.executemany(cquery, commit)
            conn.commit()
            commit = []
        cand = [word, str(pin), random.randint(1, numcombo), random.choice(names)]
        score = generate_scores()
        cand.extend(score)
        commit.append(tuple(cand))
    if commit:
        c.executemany(cquery, commit)
        conn.commit()
        print("I have generated {0} numbers".format(num))
        commit = []
    print("Registration Numbers generation completed!!")


if __name__ == '__main__':
    print("enter the number of results you want to generate, less than 15000 on local server")
    num= input()
    try:
        print("num:", num)
        num= int(num)
    except:
        print("Something went wrong... I'll try 10000 results")
        num=10000
    main(num)
    save_combos()
